CREATE OR REPLACE FUNCTION procesar_articulos(numeroCorrida integer)
RETURNS VOID
AS $$
BEGIN

    /***************************
    *  BAJA MARCAS
    ****************************/

    UPDATE marca 
    SET 
        fecha_delete = now(), 
        version = version + 1
    WHERE NOT EXISTS (
        SELECT 1 
        FROM jazz jz
        WHERE 
            jz.corrida = numeroCorrida
        AND TRIM(jz.marca) = marca.nombre
    )
    AND fecha_delete IS NULL;
      
    /***************************
    *  REHABILITACION MARCAS
    ****************************/

    UPDATE marca 
    SET 
    	fecha_update = now(),
        fecha_delete = NULL, 
        version = version + 1
    WHERE 
    	fecha_delete IS NOT NULL
    AND EXISTS (
        SELECT 1 
        FROM jazz jz
        WHERE 
            jz.corrida = numeroCorrida
        AND TRIM(jz.marca) = marca.nombre
    );
    
    /***************************
    *  ALTA MARCAS
    ****************************/

    INSERT INTO marca(id, fecha_insert, version, nombre)
    SELECT nextval('marca_id_seq'), now(), 0, aux.nombre
    FROM (
        SELECT DISTINCT TRIM(marca) AS nombre
        FROM jazz jz
        WHERE 
            jz.corrida = numeroCorrida
        AND TRIM(jz.marca) NOT IN(SELECT nombre FROM marca)
        ORDER BY
            1 ASC
    ) aux;

    /***************************
    *  BAJA RUBROS
    ****************************/

    UPDATE rubro 
    SET 
        fecha_delete = now(), 
        version = version + 1
    WHERE NOT EXISTS (
        SELECT 1 
        FROM jazz jz
        WHERE 
            jz.corrida = numeroCorrida
        AND TRIM(jz.rubro) = rubro.nombre
    )
    AND fecha_delete IS NULL;
      
    /***************************
    *  REHABILITACION RUBROS
    ****************************/

    UPDATE rubro 
    SET 
    	fecha_update = now(),
        fecha_delete = NULL, 
        version = version + 1
    WHERE 
    	fecha_delete IS NOT NULL
    AND EXISTS (
        SELECT 1 
        FROM jazz jz
        WHERE 
            jz.corrida = numeroCorrida
        AND TRIM(jz.rubro) = rubro.nombre
    );
        
    /***************************
    *   ALTA RUBROS
    ****************************/

    INSERT INTO rubro(id, fecha_insert, version, nombre)
    SELECT nextval('rubro_id_seq'), now(), 0, aux.nombre
    FROM (
        SELECT DISTINCT TRIM(rubro) AS nombre
        FROM jazz jz
        WHERE 
            jz.corrida = numeroCorrida
        AND TRIM(jz.rubro) NOT IN(SELECT nombre FROM rubro)
        ORDER BY
            1 ASC
    ) aux;

    /***************************
    *  BAJA ARTICULOS
    ****************************/

    UPDATE articulo art
    SET 
        fecha_delete = now(), 
        version = version + 1
    WHERE NOT EXISTS (
        SELECT 1 
        FROM jazz jz
        INNER JOIN marca m ON TRIM(jz.marca) = m.nombre
        INNER JOIN rubro r ON TRIM(jz.rubro) = r.nombre
        WHERE
            jz.corrida = numeroCorrida
        AND art.marca_id = m.id AND art.rubro_id = r.id AND TRIM(jz.codigo) = art.codigo
    )
    AND fecha_delete IS NULL;

    /***************************
    *  MODIFICACION ARTICULOS
    ****************************/

    UPDATE articulo art
    SET 
        fecha_update = now(), 
        fecha_delete = NULL,
        version = art.version + 1,
        descripcion = jz.descripcion,
        precio = REPLACE(jz.precio, ',', '.')::float
    FROM 
        jazz jz
    INNER JOIN marca m ON TRIM(jz.marca) = m.nombre
    INNER JOIN rubro r ON TRIM(jz.rubro) = r.nombre
    WHERE
        jz.corrida = numeroCorrida
    AND art.marca_id = m.id AND art.rubro_id = r.id AND TRIM(jz.codigo) = art.codigo;
    
    /***************************
    *  ALTA ARTICULOS
    ****************************/

    INSERT INTO articulo(id, fecha_insert, version, codigo, descripcion, marca_id, rubro_id, precio)
    SELECT nextval('articulo_id_seq'), now(), 0, aux.codigo, aux.descripcion, aux.marca_id, aux.rubro_id, aux.precio::float
    FROM (
        SELECT jz.codigo, jz.descripcion, m.id AS marca_id, r.id AS rubro_id, REPLACE(jz.precio, ',', '.') AS Precio
        FROM jazz jz
        INNER JOIN marca m ON TRIM(jz.marca) = m.nombre
        INNER JOIN rubro r ON TRIM(jz.rubro) = r.nombre
        WHERE
            jz.corrida = numeroCorrida
        AND NOT EXISTS(
            SELECT 1
            FROM articulo art
            INNER JOIN marca m ON art.marca_id = m.id
            INNER JOIN rubro r ON art.rubro_id = r.id
            WHERE TRIM(jz.marca) = m.nombre AND TRIM(jz.rubro) = r.nombre AND TRIM(jz.codigo) = art.codigo
        )
    ) aux;
    
    /***************************
    *  BAJA OBSERVACIONES
    ****************************/

    UPDATE observacion obs
    SET 
        fecha_delete = now(), 
        version = version + 1
    WHERE 
        fecha_delete IS NULL
    AND EXISTS (
        SELECT 1 
        FROM articulo art
        WHERE obs.articulo_id = art.id AND art.fecha_delete IS NOT NULL
    );
    
    /***************************
    *  ALTA OBSERVACIONES
    ****************************/

    UPDATE observacion obs
    SET 
    	fecha_update = now(), 
        fecha_delete = NULL, 
        version = version + 1
    WHERE 
        fecha_delete IS NOT NULL
    AND EXISTS (
        SELECT 1 
        FROM articulo art
        WHERE obs.articulo_id = art.id AND art.fecha_delete IS NULL
    );
    
    /***************************
    *  BAJA EQUIVALENCIAS
    ****************************/

    UPDATE equivalencia eq
    SET 
        fecha_delete = now(), 
        version = version + 1
    WHERE 
        fecha_delete IS NULL
    AND EXISTS (
        SELECT 1 
        FROM articulo art
        WHERE eq.articulo_id = art.id AND art.fecha_delete IS NOT NULL
    );
	
    /***************************
    *  ALTA EQUIVALENCIAS
    ****************************/

    UPDATE equivalencia eq
    SET 
    	fecha_update = now(), 
        fecha_delete = NULL, 
        version = version + 1
    WHERE 
        fecha_delete IS NOT NULL
    AND EXISTS (
        SELECT 1 
        FROM articulo art
        WHERE eq.articulo_id = art.id AND art.fecha_delete IS NULL
    );

    /***************************
    *  	MARCA - MODELO
    ****************************/
    
    DELETE FROM marca_rubro;

    INSERT INTO marca_rubro
    SELECT DISTINCT marca_id, rubro_id
    FROM articulo
    WHERE fecha_delete IS NULL;

    /***************************
    *  	 BAJA DE INDICES
    ****************************/
    
    UPDATE indice i
    SET
    	fecha_delete = now(), 
        version = version + 1
    WHERE
    	fecha_delete IS NULL
    AND NOT EXISTS (
        SELECT 1 
        FROM marca_rubro mr
        WHERE mr.marca_id = i.marca_id AND mr.rubro_id = i.rubro_id
    );
    
    /***************************
    *  	 ALTA DE INDICES
    ****************************/    
    
    UPDATE indice i
    SET
    	fecha_update = now(), 
        fecha_delete = NULL, 
        version = version + 1,
        margen = 0,
        descuento = 0
    WHERE
    	fecha_delete IS NOT NULL
    AND EXISTS (
        SELECT 1 
        FROM marca_rubro mr
        WHERE mr.marca_id = i.marca_id AND mr.rubro_id = i.rubro_id
    );
    
    /***************************
    *  TERMINO PROCESO
    ****************************/

    UPDATE jazz SET procesado = 1 WHERE procesado = 0 AND corrida = numeroCorrida;

    RETURN;
END;
$$ LANGUAGE plpgsql;
